from datetime import datetime
from os import path
from os import remove as fileRemove
from traceback import format_exc
from typing import Optional

from fastapi import APIRouter, Query, WebSocket, WebSocketDisconnect, status
from fastapi.responses import FileResponse
from starlette.background import BackgroundTask
from sqlalchemy import delete, select, update, tuple_
from sqlalchemy.orm import load_only
from openpyxl import Workbook
from loguru import logger

from config import settings
from schemas.base import R
from core.database import getDB
from core.exceptions import NormalException
from models.device import DeviceTable, PLCPointTable
from schemas.device import (
    CheckItemInSchema, CheckOutPoint, CheckResultItem, PointInSchema, 
    PointOutSchema, PointUpdateSchema, CheckOutItemSchema,
    CheckSuccessPoint, CheckFailPoint, BatchInputResult, RTPointInSchema, RTPointOutSchema
)
from core.device_monitor import getDeviceModel, reloadDeviceModel

router = APIRouter(prefix="/point", tags=["点位操作接口"])


@router.get(
    "/list/",
    summary="获取设备点位组下的所有点",
    response_model=R[list[PointOutSchema]]
)
def getDevicePoints(
    no: str = Query(title="设备编号"),
    group: Optional[str] = Query(default=None, title="点位组名", description="点位组为空时查询设备所有点")
):
    with getDB() as db:
        query = select(PLCPointTable).where(PLCPointTable.deviceNo == no)
        if group:
            query = query.where(PLCPointTable.group == group)
        points = db.scalars(
            query.order_by(PLCPointTable.func, PLCPointTable.addr)
        ).fetchall()
    return R(data=[
        p for p in points
    ])


@router.post(
    "/",
    summary="新建设备点位",
)
def createPoint(
    data: PointInSchema,
    no: str = Query(default=..., title="设备编号"),
    group: str = Query(default=..., title="点位组名"),
):
    if not no or not group:
        raise NormalException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="请指定设备" if no else "请选择分组或输入新分组名称"
        )
    with getDB() as db:
        deviceExist = db.scalars(select(DeviceTable).where(DeviceTable.no == no)).one_or_none()
        if not deviceExist:
            raise NormalException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="设备不存在"
            )
        pointNameExist = db.scalars(select(PLCPointTable).where(
            PLCPointTable.deviceNo == no,
            PLCPointTable.group == group,
            PLCPointTable.name == data.name
        )).one_or_none()
        if pointNameExist:
            raise NormalException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail=f"点位名称 {data.name} 已存在，请勿重复创建"
            )
        point = PLCPointTable(
            deviceNo=no,
            group=group,
            **data.model_dump()
        )
        db.add(point)
        db.commit()
    reloadDeviceModel(no)
    return R()


@router.put(
    "/",
    summary="修改点位信息",
    response_model=R
)
def editPoint(
    data: PointUpdateSchema,
    id: str = Query(title="点位索引"),
):
    with getDB() as db:
        pointExist = db.scalars(select(PLCPointTable).where(PLCPointTable.id == id)).one_or_none()
        if not pointExist:
            raise NormalException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="点位不存在"
            )
        db.execute(update(PLCPointTable).where(
            PLCPointTable.id == id
        ).values(**data.model_dump(exclude_none=True)))
        db.commit()
    reloadDeviceModel(pointExist.deviceNo)
    return R()


@router.delete(
    "/",
    summary="删除指定点位",
    response_model=R
)
def deletePoint(id: int = Query(title="点位id")):
    with getDB() as db:
        pointExist = db.scalars(select(PLCPointTable).where(PLCPointTable.id == id)).one_or_none()
        if not pointExist:
            raise NormalException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="点位不存在"
            )
        db.execute(delete(PLCPointTable).where(PLCPointTable.id == id))
        db.commit()
    reloadDeviceModel(pointExist.deviceNo)
    return R()


@router.post(
    "/batch/check/",
    summary="检查点位是否可以导入，并进行导入",
)
def checkPoints(data: list[CheckItemInSchema]):
    with getDB() as db:
        # 获取现有设备的所有编号
        deviceExist = db.scalars(select(DeviceTable.no)).fetchall()
        # 获取每个设备下的所有分组
        groupMap = {dev: [] for dev in deviceExist}
        groups = db.scalars(select(PLCPointTable).options(load_only(
            PLCPointTable.deviceNo, PLCPointTable.group
        ))).fetchall()
        for group in groups:
            groupMap[group.deviceNo].append(group.group)
        # 查找是否存在重复的点位
        points = []
        for dev in data:
            for p in dev.points:
                points.append((dev.deviceNo, p.group, p.name))
        pointExist = db.scalars(select(PLCPointTable).options(
            load_only(PLCPointTable.deviceNo, PLCPointTable.group, PLCPointTable.name)
        ).where(
            tuple_(PLCPointTable.deviceNo, PLCPointTable.group, PLCPointTable.name).in_(points)
        )).fetchall()
        pointTable = [f"{p.deviceNo}-{p.group}-{p.name}" for p in pointExist]
    result = []
    for d in data:
        pNames = [str(p.group).strip() + str(p.name).strip() for p in d.points]
        item = CheckOutItemSchema(points=[])
        if d.deviceNo not in deviceExist:
            item.deviceNo = CheckResultItem(value=d.deviceNo, level=2, tip="未发现该设备，请先创建设备后再执行导入")
        else:
            item.deviceNo = CheckResultItem(value=d.deviceNo)
        for p in d.points:
            outPoint = CheckOutPoint()
            # 分组名校验
            if p.group is not None:
                _group = str(p.group).strip()
                if not d.deviceNo:
                    pointTip = CheckResultItem(value=_group, level=2, tip="设备名称不允许为空")
                elif d.deviceNo not in deviceExist:
                    pointTip = CheckResultItem(value=_group, level=2, tip="查无设备，分组无法被创建")
                elif not _group in groupMap[d.deviceNo]:
                    pointTip = CheckResultItem(value=_group, level=1, tip="设备中查无该分组，它将被创建")
                else:
                    pointTip = CheckResultItem(value=_group)
            else:
                pointTip = CheckResultItem(level=2, tip="分组名不允许为空")
            outPoint.group = pointTip
            # 点位名校验
            if p.name is not None:
                _name = str(p.name).strip()
                nameTag = str(p.group).strip() + _name
                if not d.deviceNo:
                    pointTip = CheckResultItem(value=_name, level=2, tip="查无设备，点位无效")
                elif pNames.count(nameTag) > 1:
                    pointTip = CheckResultItem(value=_name, level=1, tip="导入表存在重复点位，系统只会导入最后一个")
                elif f"{d.deviceNo}-{p.group}-{p.name}" in pointTable:
                    pointTip = CheckResultItem(value=_name, level=2, tip="设备已存在相同点位，无法重复添加")
                else:
                    pointTip = CheckResultItem(value=_name)
            else:
                pointTip = CheckResultItem(level=2, tip="分组名不允许为空")
            outPoint.name = pointTip
            # 功能码校验
            if p.func is not None:
                _func = str(p.func).strip().upper()
                if not _func in settings.FUNC_10 + settings.FUNC_16:
                    pointTip = CheckResultItem(value=_func, level=2, tip="非法功能码")
                else:
                    pointTip = CheckResultItem(value=_func)
            else:
                _func = None
                pointTip = CheckResultItem(level=2, tip="功能码不允许为空")
            outPoint.func = pointTip
            # 地址校验
            if p.addr is not None:
                _addr = str(p.addr).strip().upper()
                pointTip = CheckResultItem(value=_addr)
                if _func and _func in settings.FUNC_10:
                    if not _addr.isdigit():
                        pointTip.level = 2
                        pointTip.tip= "非法的10进制地址"
                elif _func and _func in settings.FUNC_16:
                    if any([s not in "1234567890ABCDEF" for s in _addr]):
                        pointTip.level = 2
                        pointTip.tip= "非法的16进制地址"
                else:
                    pointTip = CheckResultItem(value=_addr, level=1, tip="未定义正确的功能码")
            else:
                pointTip = CheckResultItem(level=2, tip="地址不允许为空")
            outPoint.addr = pointTip
            if p.desc:
                outPoint.desc = p.desc.strip()
            item.points.append(outPoint)
        for p in item.points:
            if p.addr.level == 1 or p.name.level == 1 or p.group.level == 1 or p.func.level == 1:
                if item.deviceNo.level is not None and item.deviceNo.level < 1:
                    item.deviceNo.level = 1
                    item.deviceNo.tip = "部分点位可能存在问题"
            if p.addr.level == 2 or p.name.level == 2 or p.group.level == 2 or p.func.level == 2:
                if item.deviceNo.level is not None and item.deviceNo.level < 2:
                    item.deviceNo.level = 2
                    item.deviceNo.tip = "部分点位存在严重问题，可能无法正确导入"
        result.append(item)
    return R(data=result)


@router.post(
    "/batch/input/",
    summary="批量导入点位"
)
def pointsBatchInput(data: list[CheckItemInSchema]):
    with getDB() as db:
        # 获取现有设备的所有编号
        deviceExist = db.scalars(select(DeviceTable.no)).fetchall()
        # 获取所有相关的点位
        points = []
        for dev in data:
            for p in dev.points:
                points.append((dev.deviceNo, p.group, p.name))
        pointExist = db.scalars(select(PLCPointTable).options(
            load_only(PLCPointTable.deviceNo, PLCPointTable.group, PLCPointTable.name)
        ).where(
            tuple_(PLCPointTable.deviceNo, PLCPointTable.group, PLCPointTable.name).in_(points)
        )).fetchall()
        pointTable = [f"{p.deviceNo}-{p.group}-{p.name}" for p in pointExist]
        count = 0  # 插入计数，0 总数，1 成功数，2 失败数
        successList: list[CheckSuccessPoint] = []
        failList: list[CheckFailPoint] = []
        for d in data:
            count += len(d.points)
            if not d.deviceNo or (d.deviceNo not in deviceExist):
                for p in d.points:
                    failList.append(
                        CheckFailPoint(deviceNo=d.deviceNo, **p.model_dump())
                    )
                continue
            srcExistPoints = [f"{str(p.group).strip()}-{str(p.name).strip()}" for p in d.points]
            for p in d.points:
                failItem = CheckFailPoint(deviceNo=d.deviceNo, **p.model_dump())
                # 判断数据是否已存在数据库中
                if f"{d.deviceNo}-{p.group}-{p.name}" in pointTable:
                    failList.append(failItem)
                    continue
                # 判断数据是否在表中有重复
                pointTag = f"{str(p.group).strip()}-{str(p.name).strip()}"
                if srcExistPoints.count(pointTag) > 1:
                    failList.append(failItem)
                    index = srcExistPoints.index(pointTag)
                    srcExistPoints.pop(index)
                    continue
                # 判断有空数据的项直接忽略，不进行插入
                if not p.group or not p.func or not p.addr:
                    failList.append(failItem)
                    continue
                # 判断功能码是否合法
                _func = str(p.func).strip().upper()
                if not _func in settings.FUNC_10 + settings.FUNC_16:
                    failList.append(failItem)
                    continue
                # 判断地址是否合法
                _addr = str(p.addr).strip().upper()
                if _func in settings.FUNC_10:
                    if not _addr.isdigit():
                        # 忽略非法十进制数
                        failList.append(failItem)
                        continue
                elif _func in settings.FUNC_16:
                    if any([s not in "1234567890ABCDEF" for s in _addr]):
                        # 忽略非法十六进制数
                        failList.append(failItem)
                        continue
                successList.append(CheckSuccessPoint(
                    deviceNo=d.deviceNo,
                    **p.model_dump()
                ))
        db.add_all([PLCPointTable(**p.model_dump()) for p in successList])
        db.commit()
    for dev in deviceExist:
        reloadDeviceModel(dev)
    return R(data=BatchInputResult(
        count=count,
        successCount=len(successList),
        failCount=len(failList),
        failList=failList,
        successList=successList
    ))


@router.get(
    "/batch/output/excel/",
    summary="下载网关的所有点位并导出为excel"
)
def ouputAllPointsExcel():
    with getDB() as db:
        points = db.scalars(select(PLCPointTable).order_by(PLCPointTable.group)).fetchall()
    wb = Workbook()
    ws = wb.active
    if ws:
        wb.remove_sheet(ws)
    pointMap = {}
    for point in points:
        if not pointMap.get(point.deviceNo):
            pointMap[point.deviceNo] = []
        pointMap[point.deviceNo].append(point.dict(exclude=["deviceNo"]))
    for k, v in pointMap.items():
        ws = wb.create_sheet(k)
        ws.append(["分组名称", "点位代号", "描述信息", "功能码", "地址"])
        for item in v:
            ws.append([item["group"], item["name"], item["desc"], item["func"], item["addr"]])
    fileName = f"设备点位表{int(datetime.now().timestamp())}.xlsx"
    filePath = path.join(settings.STATIC_DIR, fileName)
    wb.save(filePath)
    return FileResponse(
        filePath, filename=fileName, 
        background=BackgroundTask(lambda: fileRemove(filePath))
    )


@router.websocket("/ws/")
async def wsCommunication(websocket: WebSocket):
    await websocket.accept()
    try:
        while True:
            srcData = await websocket.receive_json()
            data = RTPointInSchema(**srcData)
            dev = getDeviceModel(data.no)
            if dev:
                groups = {}
                for g in data.groups:
                    groups[g] = {
                        pName: p.value for pName, p in dev.model.g[g].p.items()
                    } if dev.model.g.get(g) else None
                result = RTPointOutSchema(
                    no=data.no, connectStatus=dev.status, 
                    groups=groups
                )
            else:
                result = RTPointOutSchema(no=data.no)
            await websocket.send_json(result.model_dump())
    except WebSocketDisconnect:
        return
    except:
        logger.error(format_exc())
        return